package com.gack.business.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.gack.business.vo.ListOrders;
import com.gack.business.vo.OrderVo;
import com.gack.business.vo.UserDetailVO;
import com.gack.business.vo.UserItemVO;

import io.jsonwebtoken.lang.Collections;

/**
 * 
 * @author ws
 * 2018-5-31
 */
@Repository
@Transactional
public class UserDao {

	@PersistenceContext
	private EntityManager em;
	
	public Object findRecordByuserId(String userId){
		String sql = "SELECT uuid as id,SUM(amout) as amout,kind,type,create_time,name,info FROM invoice WHERE uid = :uid GROUP BY uuid ORDER BY modified_time DESC";
		Query query = em.createNativeQuery(sql);
		if(userId == null)
			return null;
		query.setParameter("uid", userId);
		return query.getResultList();
	}
	
	public ListOrders findOrdersByUserId(String userId,int page,int pageSize){
		String jpql = "select new com.gack.business.vo.OrderVo(o.id,(SELECT storeName from VideoStores s where s.id = o.storeId) as storeName,o.createdTime,o.beginTime,o.endTime,o.amount,o.status) from Order o where o.userId = :userId"
				+ " and o.status != '2' and o.status != '3'  order by createdTime desc";
		
		Query query1 = em.createQuery(jpql,OrderVo.class);
		Query query2 = em.createQuery(jpql,OrderVo.class);
		
		query1.setParameter("userId", userId);
		query2.setParameter("userId", userId);
		
		int size = query2.getResultList().size();
		
		List<OrderVo> vos = query1.setFirstResult(page * pageSize).setMaxResults(pageSize).getResultList();
		vos.forEach(vo -> {
			vo.setAmount((vo.getAmount() == null ? 0 : vo.getAmount()) / 100);
		});
		return new ListOrders(vos,size);
	}	
	
	/**
	 * 查询某个用户在某个公司下的详情
	 * @param enterpriseId 公司id
	 * @param userId 被查询的用户id
	 * @param showToUserId 向谁展示的用户id
	 */

	public UserDetailVO findUserDetailVO(String enterpriseId, String userId, String showToUserId){
		String jpql = "select u.id,"
				+ " u.portrait,"
				+ " case when (select friend_remarks"
							+ " from contacts" 
							+ " where user_username=(select username from user where id=:showToUserId)"
							+ " and userid_friend_id=u.id"
							+ " and type=1) is not null"
							+ " and (select trim(friend_remarks)"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:showToUserId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1) != ''"
						+ " then (select friend_remarks"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:showToUserId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1)"
				+ " else (select enterprise_remarks"
							+ " from user_enterprise_department_position"
							+ " where enterprise_id=:enterpriseId"
							+ " and user_id=u.id)"
				+ " end as name_for_show,"
				+ " u.nickname,"
				+ " u.username,"
				+ " (select name from enterprise where id=:enterpriseId) as enterprise_name,"
				+ " (select enterprise_remarks from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=:userId) as enterprise_remarks,"
				+ " '' as role_id,"
				+ " (select department_id from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=:userId) as department_name,"
				+ " (select position_id from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=:userId) as position_name"
				+ " from user u"
				+ "	where u.id=:userId";
		
		Query query = em.createNativeQuery(jpql, UserDetailVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		query.setParameter("showToUserId", showToUserId);
		if(query.getResultList().size() == 0){
			return null;
		}
		
		return (UserDetailVO)query.getSingleResult();
	}
	
	/**
	 * 根据查询名称,查找当前公司的用户
	 * 			规则为:输入的查询名称应匹配用户应显示的名称(好友备注名或企业备注名或昵称)
	 * @param userId 操作者id
	 * @param enterpriseId 公司id
	 * @param inputName 用户输入的查询名称
	 * @return 
	 */
	public List<UserItemVO> findUserItemVOByInputName(String userId, String enterpriseId, String inputName){
		String jpql = "select id,"
					+ " username,"
					+ " portrait,"
					+ " case when (select friend_remarks"
							+ " from contacts" 
							+ " where user_username=(select username from user where id=:userId)"
							+ " and userid_friend_id=u.id"
							+ " and type=1) is not null"
							+ " and (select trim(friend_remarks)"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:userId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1) != ''"
							+ " then (select friend_remarks"
									+ " from contacts" 
									+ " where user_username=(select username from user where id=:userId)"
									+ " and userid_friend_id=u.id"
									+ " and type=1)"
							+ " else (select enterprise_remarks"
										+ " from user_enterprise_department_position"
										+ " where enterprise_id=:enterpriseId"
										+ " and user_id=u.id)"
				+ " end as name_for_show,"
				+ " '' as role_id"
				+ " from user u"
				+ " where u.id in (select DISTINCT user_id"
								+ " from (select user_id"
										+ " from user_enterprise_department_position"
										+ " where enterprise_id=:enterpriseId"
										+ " and enterprise_remarks like :inputName"
										+ " UNION"
										+ " select userid_friend_id as user_id"
										+ " from contacts" 
										+ " where user_username=(select username from user where id=:userId)"
										+ " and type=1"
										+ " and friend_remarks like :inputName"
										+ "	and userid_friend_id in (select user_id"
																	+ " from user_enterprise_department_position"
																	+ " where enterprise_id=:enterpriseId)"
										+ " ) as a"
								+ ")";
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		query.setParameter("inputName", "%" + inputName + "%");
		return query.getResultList();
	}
	
	/**
	 * 根据查询手机号,查找当前公司用户(模糊查询)
	 * @param userId 操作者id
	 * @param enterpriseId 公司id
	 * @param inputName 用户输入的查询手机号
	 * @return 
	 */
	public List<UserItemVO> findUserItemVOByInputTel(String userId, String enterpriseId, String inputTel){
		String jpql = "select id,"
					+ " username,"
					+ " portrait,"
					+ " case when (select friend_remarks"
							+ " from contacts" 
							+ " where user_username=(select username from user where id=:userId)"
							+ " and userid_friend_id=u.id"
							+ " and type=1) is not null"
							+ " and (select trim(friend_remarks)"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:userId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1) != ''"
							+ " then (select friend_remarks"
									+ " from contacts" 
									+ " where user_username=(select username from user where id=:userId)"
									+ " and userid_friend_id=u.id"
									+ " and type=1)"
							+ " else (select enterprise_remarks"
										+ " from user_enterprise_department_position"
										+ " where enterprise_id=:enterpriseId"
										+ " and user_id=u.id)"
				+ " end as name_for_show,"
				+ " '' as role_id"
				+ " from user u"
				+ " where u.username like :inputTel"
				+ " and u.id in ("
							+ " select user_id"
							+ " from user_enterprise_department_position"
							+ " where enterprise_id=:enterpriseId"
							+ " )";
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		query.setParameter("inputTel", "%" + inputTel + "%");
		return query.getResultList();
	}
	
	/**
	 * 用户查询无部门的人员信息
	 * @param enterpriseId 企业id
	 * @param userId 操作者id
	 * @return
	 */
	public List<UserItemVO> findNoDepartmentUserItemVO(String enterpriseId, String userId){
		String jpql = "select id,"
					+ " username,"
					+ " portrait,"
					+ " case when (select friend_remarks"
							+ " from contacts" 
							+ " where user_username=(select username from user where id=:userId)"
							+ " and userid_friend_id=u.id"
							+ " and type=1) is not null"
							+ " and (select trim(friend_remarks)"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:userId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1) != ''"
							+ " then (select friend_remarks"
									+ " from contacts" 
									+ " where user_username=(select username from user where id=:userId)"
									+ " and userid_friend_id=u.id"
									+ " and type=1)"
							+ " else (select enterprise_remarks"
										+ " from user_enterprise_department_position"
										+ " where enterprise_id=:enterpriseId"
										+ " and user_id=u.id)"
				+ " end as name_for_show,"
				+ " '' as role_id"
				+ " from user u"
				+ " where u.id in (select user_id"
								+ " from user_enterprise_department_position"
								+ " where enterprise_id=:enterpriseId"
								+ " and (department_id is null or trim(department_id) = ''))";
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		return query.getResultList();
	}
	
	/**
	 * 查询某些部门下的人员信息
	 * @param enterpriseId 企业id
	 * @param userId 操作者id
	 * @return
	 */
	public List<UserItemVO> findUserItemVOByDepartmentIdList(String enterpriseId, List<String> departmentIdList, String userId){
		
		if(Collections.isEmpty(departmentIdList)){
			return new ArrayList<UserItemVO>();
		}
		
		String jpql = "select id,"
					+ " username,"
					+ " portrait,"
					+ " case when (select friend_remarks"
							+ " from contacts" 
							+ " where user_username=(select username from user where id=:userId)"
							+ " and userid_friend_id=u.id"
							+ " and type=1) is not null"
							+ " and (select trim(friend_remarks)"
								+ " from contacts" 
								+ " where user_username=(select username from user where id=:userId)"
								+ " and userid_friend_id=u.id"
								+ " and type=1) != ''"
							+ " then (select friend_remarks"
									+ " from contacts" 
									+ " where user_username=(select username from user where id=:userId)"
									+ " and userid_friend_id=u.id"
									+ " and type=1)"
							+ " else (select enterprise_remarks"
										+ " from user_enterprise_department_position"
										+ " where enterprise_id=:enterpriseId"
										+ " and user_id=u.id)"
				+ " end as name_for_show,"
				+ " '' as role_id"
				+ " from user u"
				+ " where u.id in (select user_id"
								+ " from user_enterprise_department_position"
								+ " where department_id in ";
		
		StringBuilder sb = new StringBuilder();
		sb.append("(");
		for(String t : departmentIdList){
			 sb.append(" '" + t + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));//去掉最后的","
		sb.append(") )");//最后一个括号是子查询的右括号
		
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql, UserItemVO.class);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		return query.getResultList();
	}
	
	/**
	 * 获取用户当天意见反馈数量
	 */
	public int getUserTodayOpinionCount(String userid,String source){
		String jpql = "select"
						+ " count(id)"
					+ " from opinion"
					+ " where"
						+ " create_time >= DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')"
						+ " and userid = :userid"
						+ " and source = :source";
		Query query = em.createNativeQuery(jpql);
		query.setParameter("userid",userid);
		query.setParameter("source",source);
		return ((BigInteger)query.getSingleResult()).intValue();
	}
	
	/**
	 * 手机号获取用户是否注册
	 */
	public int getUserCountByUsername(String username){
		String jpql = "select"
						+ " count(id)"
					+ " from user"
					+ " where"
						+ " username = :username";
		Query query = em.createNativeQuery(jpql);
		query.setParameter("username",username);
		return ((BigInteger)query.getSingleResult()).intValue();
	}
	
	/**
	 * 查询某些人员在某公司下的nameForShow(好友备注>企业备注)
	 * @param enterpriseId 公司id
	 * @param userIds 查询的人员id
	 * @param userId 操作者id
	 * @return
	 */
	public List<Object[]> findUserIdAndNameForShow(String enterpriseId, List<String> userIds, String userId){
		
		if(Collections.isEmpty(userIds)){
			return new ArrayList<Object[]>();
		}
		
		String jpql = "select id,"
				+ " case when (select friend_remarks"
				+ " from contacts" 
				+ " where user_username=(select username from user where id=:userId)"
				+ " and userid_friend_id=u.id"
				+ " and type=1) is not null"
				+ " and (select trim(friend_remarks)"
					+ " from contacts" 
					+ " where user_username=(select username from user where id=:userId)"
					+ " and userid_friend_id=u.id"
					+ " and type=1) != ''"
				+ " then (select friend_remarks"
						+ " from contacts" 
						+ " where user_username=(select username from user where id=:userId)"
						+ " and userid_friend_id=u.id"
						+ " and type=1)"
				+ " else (select enterprise_remarks"
							+ " from user_enterprise_department_position"
							+ " where enterprise_id=:enterpriseId"
							+ " and user_id=u.id)"
				+ " end as name_for_show"
				+ " from user u"
				+ " where u.id in";
		
		StringBuilder sb = new StringBuilder();
		sb.append("(");
		for(String t : userIds){
			 sb.append(" '" + t + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));//去掉最后的","
		sb.append(")");
		
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		query.setParameter("userId", userId);
		return query.getResultList();
	}
	
	/**
	 * 根据用户手机号批量查找用户id
	 * @param usernames 手机号集合
	 * @return
	 */
	public List<String> findIdByUsername(List<String> usernames){
		
		if(Collections.isEmpty(usernames)){
			return new ArrayList<String>();
		}
			
		String jpql = "select id"
				+ " from User"
				+ " where username in (";
		
		StringBuilder sb = new StringBuilder();
		for(String t : usernames){
			 sb.append(" '" + t + "',");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-1));//去掉最后的","
		sb.append(")");
		
		jpql += sb.toString();
		
		Query query = em.createQuery(jpql);
		return query.getResultList();
	}
	
}
